package awConnectionService;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;

import entities.Device;
import entities.DeviceType;

public class DBConnect {
//	private static final String driver = "com.mysql.jdbc.Driver";
	
//	private String url = "jdbc:mysql://localhost:3306";
//	private String userName = "root";
//	private String password = "alfa2082";
	private String dbName = "capstone";
	
	private Connection con = null;
	
	public DBConnect(Connection con){
		this.con = con;
	}

	
	public Device getDeviceByUUID(String UUID){
		System.out.println("DBConnect: getDeviceByUUID()");
		Device device = new Device();
		device.setDeviceName("NOTFOUND");

		try {
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName +";");
			ResultSet rs = stat.executeQuery("SELECT * FROM devices WHERE uuid = '" + UUID + "';");
			rs.next();
			
			device.setDeviceName(rs.getString("name"));
			device.setDeviceType(DeviceType.valueOf(rs.getString("type")));
			device.setIpAddress(rs.getString("ip"));
			device.setUUID(rs.getString("uuid"));
			device.setConnectedDevices(getConnections(device.getUUID()));
			
			stat.close();
		} catch (SQLException e) {e.printStackTrace();}
		
		return device;
	}
	public Device getDeviceByIp(String IP){
		System.out.println("DBConnect: getDeviceByIp()");
		Device device = new Device();
		
		try{
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			
			ResultSet rs = stat.executeQuery("SELECT * FROM devices WHERE ip = '" + IP + "';");
			rs.next();

			device.setDeviceName(rs.getString("name"));
			device.setDeviceType(DeviceType.valueOf(rs.getString("type")));
			device.setIpAddress(rs.getString("ip"));
			device.setUUID(rs.getString("uuid"));
			device.setConnectedDevices(getConnections(device.getUUID()));
			
			stat.close();
		}catch(SQLException ex){ex.printStackTrace();}

		return device;
	}
	
	public void insertDevicePair(String uuid, String pairKey){
		System.out.println("DBConnect: insertDevicePair()");
		try {
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			
			stat.executeUpdate("INSERT INTO devicepair (device_uuid, device_pairkey) VALUE('" + uuid + "', '" + pairKey + "');");
			
		} catch (SQLException e) {e.printStackTrace();
		}
	}
	public void removeDevicePair(String uuid){
		System.out.println("DBConnect: removeDevicePair()");
		try {
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			stat.executeUpdate("DELETE FROM devicepair WHERE device_uuid = '" + uuid + "';");
		} catch (SQLException e) {e.printStackTrace();
		}
	}
	public void updateDevicePair(String uuid, String pairKey){
		removeDevicePair(uuid);
		insertDevicePair(uuid, pairKey);
	}
	public String getDevicePair(String id){
		System.out.println("DBConnect: getDevicePair()");
		String pairKey = "";
		try {
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			
			System.out.println(id);
			ResultSet rs = stat.executeQuery("SELECT * FROM devicePair WHERE device_uuid = '" + id + "';");
			rs.next();
			
			pairKey = rs.getString("device_pairkey");
		} catch (SQLException e) {e.printStackTrace();
		}
		return pairKey;
	}
	
	public ArrayList<Device> getDevicesByUser(String userId){
		System.out.println("DBConnect: getDevicesByUser()");
		ArrayList<Device> devices = new ArrayList<Device>();
		
		try{
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			
			ResultSet userdevices = stat.executeQuery("SELECT * FROM userdevices WHERE userid = '" + userId + "';");
			ArrayList<String> deviceIds = new ArrayList<String>();
			while(userdevices.next()){
				deviceIds.add(userdevices.getString("deviceuuid"));
			}
					
			for(String s : deviceIds){
				ResultSet userDevice = stat.executeQuery("SELECT * FROM devices WHERE uuid = '" + s + "';");
				while(userDevice.next()){
					Device device = new Device();
					
					device.setDeviceName(userDevice.getString("name"));
					device.setDeviceType(DeviceType.valueOf(userDevice.getString("type")));
					device.setIpAddress(userDevice.getString("ip"));
					device.setUUID(userDevice.getString("uuid"));
					device.setConnectedDevices(getConnections(device.getUUID()));
					
					devices.add(device);
				}
			}
			
			stat.close();
		}catch(SQLException ex){ex.printStackTrace();}
		
		return devices;
	}
	

	public void insertDevice(Device device){
		System.out.println("DBConnect: insertDevice()");
		try {
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			stat.executeUpdate(
					"INSERT INTO devices (uuid, name, ip, type) VALUES('" + device.getUUID() +
					"', '" + device.getDeviceName() + "', '" + device.getIpAddress() + "', '" + device.getDeviceType() + "');");
			
			if(device.getConnectedDevices() != null){
				if(device.getConnectedDevices().size() >= 1){
					if(!device.getConnectedDevices().get(0).equals("")){
						for(String s : device.getConnectedDevices()){
							insertConnection(device.getUUID(), s);
						}
					}
				}
			}
			stat.close();
		} catch (SQLException e) {e.printStackTrace();}
	}	
	public void updateDevice(Device device){
		System.out.println("DBConnect: updateDevice()");
		removeDevice(device.getUUID());
		insertDevice(device);		
	}	
	public void removeDevice(String UUID){
		System.out.println("DBConnect: removeDevice()");
		try{
			//Removes Connections
			Device removing = getDeviceByUUID(UUID);
			for(String uuid : removing.getConnectedDevices()){
				removeConnection(removing.getUUID(), uuid);
			}
			//Removes Device
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			stat.executeUpdate("DELETE FROM devices"+ 
					" WHERE uuid" + " = '" + UUID + "';");
			stat.close();
			
		}catch(SQLException e) {e.printStackTrace();}
	}
	public void removeDevicesByUser(String userid){
		System.out.println("DBConnect: removeDevicesByUser()");
		ArrayList<Device> devices = getDevicesByUser(userid);
		for(Device d : devices){
			removeDevice(d.getUUID());
		}
	}

	private ArrayList<String> getConnections(String hostUUID){
		System.out.println("DBConnect: getConnections()");
		ArrayList<String> found = new ArrayList<String>();
		
		try {
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName +";");
			ResultSet rs = stat.executeQuery("SELECT connecteduuid FROM connections WHERE hostuuid = '" + hostUUID + "';");
			while(rs.next()){
				String s = rs.getString("connecteduuid");
				found.add(s);
			}
		} catch (SQLException e) {e.printStackTrace();}
		
		return found;
	}

	private void insertConnection(String hostUUID, String connectedUUID){
		System.out.println("DBConnect: insertConnection()");
		try{
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			stat.executeUpdate(
					"INSERT INTO connections (hostuuid, connecteduuid) VALUES('" +
					hostUUID + "', '" + connectedUUID + "');");
			stat.close();
		}catch(SQLException ex){ex.printStackTrace();}
	}
	private void removeConnection(String hostUUID, String connectedUUID){
		System.out.println("DBConnect: removeConnection");
		try{
			Statement stat = con.createStatement();
			stat.execute("USE " + dbName + ";");
			stat.executeUpdate("DELETE FROM connections WHERE hostuuid = '" + hostUUID + "' AND connecteduuid = '" + connectedUUID + "';");
			stat.close();
		}catch(SQLException ex){ex.printStackTrace();}
	}
	
	

}
